package top.went.db.dao;


import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.CrudRepository;
import top.went.pojo.PaymentRecordsEntity;
import top.went.pojo.PlanPayDetailEntity;

import java.sql.Time;
import java.util.Date;
import java.util.List;

/**
 * 计划付款Dao
 */
public interface PlanPayDetailDao extends JpaRepository<PlanPayDetailEntity,Integer> {
    /**
     * 查询所有付款计划
     * @return
     */
    public List<PlanPayDetailEntity> findAllByMagicDeleteOrderByPpdIdDesc(long delete,Pageable pageable);

    /**
     * 根据计划付款日期查询所有付款
     * @param ppd_date
     * @return
     */
    @Query(nativeQuery = true,value = "SELECT * FROM TB_PLAN_PAY_DETAIL  WHERE  to_char(PPD_DATE,'yyyy-MM-dd') like ?1 and MAGIC_DELETE=0 order by ?#{#pageable}",
            countQuery = "SELECT count(*) FROM TB_PLAN_PAY_DETAIL WHERE to_char(PPD_DATE,'yyyy-MM-dd') like ?1 and magic_delete=0")
    public Page<PlanPayDetailEntity> findAllByDate(String ppd_date, Pageable pageable);

    /**
     * 根据计划付款日期查询所有付款数量
     * @return
     */
    @Query(nativeQuery = true,value = "select count(*) from tb_plan_pay_detail where magic_delete=0 order by ppd_id desc")
    public int ppd_findAllCount();

    /**
     * 根据计划付款日期查询所有付款
     * @param ppd_date
     * @return
     */

    @Query(nativeQuery = true,value = "SELECT count(*) FROM TB_PLAN_PAY_DETAIL  WHERE  to_char(PPD_DATE,'yyyy-MM-dd') like ?1 and MAGIC_DELETE=0 order by PPD_ID desc")
    public int ppd_findByPpdDateCount(String ppd_date);
    /**
     * 加载
     * @param delete
     * @param ppdId
     * @return
     */
    public PlanPayDetailEntity findAllByMagicDeleteAndPpdId(long delete, int ppdId);
    /**
     * 根据采购单统计付款金额分布
     * @return
     */
    @Query(nativeQuery = true,value = "select max(nvl(p.PUR_THEME,'其他')) TYPE, sum(nvl(o.PPD_MONEY,0)) count from tb_plan_pay_detail o left join TB_PURCHASE p on o.PUR_ID=p.PUR_ID group by o.PUR_ID")
    List<Object[]> ppdstatisticsByPur();
    /**
     * 根据年统计付款金额分布
     * @return
     */
    @Query(nativeQuery = true,value = "select nvl(to_char(o.PPD_DATE,'yyyy'),'其他') TYPE, sum(nvl(o.PPD_MONEY,0)) count from tb_plan_pay_detail o group by to_char(o.PPD_DATE,'yyyy')")
    List<Object[]> ppdstatisticsByYear();
    /**
     * 根据月统计付款金额分布
     * @return
     */
    @Query(nativeQuery = true,value = "select nvl(to_char(o.PPD_DATE,'yyyy-MM'),'其他') TYPE, sum(nvl(o.PPD_MONEY,0)) count from tb_plan_pay_detail o group by to_char(o.PPD_DATE,'yyyy-MM')")
    List<Object[]> ppdstatisticsByMonth();
    /**
     * 根据日期统计付款金额分布
     * @return
     */
    @Query(nativeQuery = true,value = "   select nvl(to_char(o.PPD_DATE,'yyyy-MM-dd'),'其他') TYPE, sum(nvl(o.PPD_MONEY,0)) count from tb_plan_pay_detail o group by to_char(o.PPD_DATE,'yyyy-MM-dd')")
    List<Object[]> ppdstatisticsByDate();

    /**
     * 根据客户统计付款金额分布
     * @return
     */
    @Query(nativeQuery = true,value = "    select max(nvl(p.CUS_NAME,'其他')) TYPE, sum(nvl(o.PPD_MONEY,0)) count from tb_plan_pay_detail o left join TB_CUSTOMER p on o.CUS_ID=p.CUS_ID group by o.CUS_ID")
    List<Object[]> ppdstatisticsByCus();
}
